Shareware Grab Bag/Shareware Grab Bag.iso/019/socsec.arc/SOCSEC.DOC

Text File | 1986-11-23 | 9.0 KB | 178 lines

SOCIAL SECURITY RETIREMENT BENEFIT CALCULATION SPREADSHEET
By EARL PATIN, 9231 ROANOKE DRIVE, EL PASO, TEXAS 79924

GENERAL INFORMATION:

1.   This spreadsheet, designed to be run with LOTUS 123, Release 1A,
may be run with that release or higher releases of LOTUS 123 and LOTUS
SYMPHONY.

2.   The formulae used in the calculations are correct as of November
1986.  Due to the political nature of the Social Security Program, we
cannot assure that this will be true at any time in the future.
Undoubtedly, they will change as Congress makes revisions.

3.   We do not guarantee that the final figures will agree exactly
with those which might be produced by the Social Security Administration.
However, they will be within the "ball-park" and could be used in
planning for the future.  As always, it would be wise to look at these
figures with a "grain of salt"--Congress may change the whole system
in the blink of an eye.

4.   Here is a brief summary of the data used in developing this
spreadsheet:

     a.  Earnings credited and used in the spreadsheet do not extend
         before 1951.  This was done to be in line with current
         procedures of the Social Security Administration, and due to
         the fact the the number of creditable years used for the
         calculations do not require data before that year.

     b.  The Primary Insurance Amount (PIA) is a three-tiered percentage
         of the Average Indexed Monthly Earnings (AIME).  The AIME is
         calculated on a variable number of years of earnings (depending
         on when you were born).  For the purposes of this spreadsheet,
         the calculations are based on the high 35 years of earnings,
         which applies to all persons born after 1928.  If the person
         for whom the calculations are being made was born before or in
         1928, a different number of years must be used.  These are:

         1916-22 1917-23 1918-24 1919-25 1920-26 1921-27 1922-28
         1923-29 1924-30 1925-31 1926-32 1927-33 1928-34

         Should this change be necessary, the cells used in calculating
         the AIME must be modified accordingly.  The AIME cell has been
         labeled in the workarea space of the spreadsheet and the
         formula used has been written to show the number of years
         used in the calculation:  "$O$1/(12*35)".  Thus, it is only
         necessary to change the '35' in the formula to the appropriate
         number of years.  Also, it necessary to change the number of
         years of indexed earnings.  The INDEXED EARNINGS cell is a
         function formula (@SUM($F$16..$F$50)) and the last cell
         referenced in the formula must be changed to include the
         same number of cells as was changed in the AIME cell.  An
         example is:  (for a person born in 1920) the AIME cell
         would be changed to "$O$1/(12*26) and the INDEXED EARNINGS
         cell would be changed to "@SUM($f$16..$f$41)".  Both cells
         may be quickly accessed by doing a "GOTO M1".

     c.  Since the earning power of the dollar has gradually eroded over
         the years, an index factor is used to determine the present
         purchasing power of previous earnings credited for the benefits.
         This index factor extends through the year of 1983.  Thereafter,
         the current value of the creditable earnings are used.  These
         index factors have been included in the spreadsheet and are
         used in the calculations to determine the benefit.  Don't
         worry about them--they are automatic.

     d.  Although due care was used in accumulating the various index
         factors, percentages, and rules for calculating, we are not
         part of the Social Security Administration, and cannot guaran-
         tee the absolute validity of such figures.  This spreadsheet
         does, however, give an estimate of the benefits which should
         be in the range of those calculated by the Administration.
         Further, it does provide you with a way of testing and making
         several runs, based on various years of retirement, and may
         help you in planning on just what additional sources you would
         have to acquire to assure a financially secure retirement.

EXAMPLE SPREADSHEET (SOCSECE.WKS)

     Included, in addition to the running spreadsheet, is a spreadsheet
entitled 'SOCSECE.WKS'.  It is a fictional spreadsheet based on the
following assumptions:

     The person for whom this is run intends to retire in the year of
2000.  He was born in the 1930's and first paid social security in
1956.  His estimated month of retirement is July 2000, thus, the last
year of estimated earnings has been calculated to June 2000.  His
earnings have been above the maximum creditable for Social Security
since he started paying the Social Security Tax and the earnings past
1986 have been estimated to be same as 1986 in order to preserve the
value of the retirement benefit in the current value of the dollar.

GATHERING INFORMATION FOR USE IN PERSONAL CALCULATIONS

     Naturally, you should use the actual earnings credited for Social
Security in running the spreadsheet for any particular individual.
This information may be from actual records or may be obtained from the
Social Security Administration.  To obtain the figures from Social
Security Administration records, obtain, from the local Social Security
Office, a copy of Form SSA-7004 and submit it to appropriate location.
In time, you will receive the earnings credited.  Even though it takes
quite a while to receive this information, do not despair--it will
eventually arrive.

RUNNING THE SPREADSHEET

     A.  Enter the yearly earnings credited for Social Security in
         the column labeled 'EARNINGS' for the year shown in the
         column labeled 'YEAR'.  Be careful and double-check these
         figures.  They should not exceed the amount shown in the
         column labeled 'MAX' for any year.

     B.  For all years subsequent to the last available figure until
         the year in which you wish to retire, use the last available
         figure (1986).  Even though the actual figures may change in
         the future years, this figure will give the benefit in the
         current value of the dollar.

     C.  Enter annual earnings only up to the year in which you intend
         to retire.  In that last year, it would be wise to enter only
         the amount for the number of months prior to retirement.  To
         do this, divide the estimated yearly earnings by 12 and
         multiply this amount by the number of months prior to the
         month in which you wish to retire.  This will make the final
         figure closer to actuality.

     D.  After all the earnings have been entered, tell 123 to 'GO'
         by holding down the <ALT> key and hitting the 'G' key.  This
         will enable a group of macros which will perform the yearly
         index calculations, abstract and total the high 35 years of
         earnings, calculate the AIME, calculate the PIA, calculate
         the amount of individual taxes paid for each year, and,
         finally, calculate and enter the applicable totals in the
         appropriate sections of the spreadsheet ahead of the yearly
         figures.

         REMEMBER:  <ALT>G to run

     E.  If you wish to have a hardcopy printout of the main area
         of the spreadsheet, hold down the <ALT> key and hit 'P' to
         cause automatic printing of the calculated data.

         REMEMBER:  <ALT>P to print

     F.  If you wish to electronically save the spreadsheet, hold
         down the <ALT> key and hit 'S'.  This will save the spread-
         sheet under the name of "SOCSEC.WKS".

         REMEMBER:  <ALT>S to save

     G.  To go on to retrieve other worksheets from your 123 database,
         hold down the <ALT> key and hit 'R'.  This will cause 123
         to bring up the available spreadsheets in your data section
         for selection and use.

         REMEMBER:  <ALT>R to retrieve

     H.  Finally, to leave 123 after using this spreadsheet, hold
         down the <ALT> key and hit 'Q'.  This will cause 123 to
         return to DOS.

         REMEMBER:  <ALT>Q to quit

ADDITIONAL FACTS ABOUT THE SPREADSHEET

     The main area of the spreadsheet (Columns A through G) contain
information of interest to the recipient of the information.  In
addition, there are work areas, although necessary for running the
calculations, are usually not of interest to the person who is only
interested in his personal data.  These figures include the cells
for calculating the high 35 years of earnings, the AIME, the PIA,
and the macros necessary to run.  Should you be interested in these
(especially if it is necessary to change the number of years used
in the calculations), you will find them starting in cell M1.  A
GOTO cell M1 will display all of the work areas and macros used
in the spreadsheet.